<?php
/*
 * Copyright (c) Taras Kudla, 2010
 */

# last checking: 15.03.2010, status:worked
class mysql extends kernel implements dbostruct
  {
    private $rConnection = null;
    private $DefaultEngine = "MyISAM";
    private $DefaultCharset = "utf8";
    private $database = null;
    
    # mysql object initialization method
    public function init(){
       # checking for extension
       if (!extension_loaded("mysql"))
            $this->core->error("DBO_MYSQL", "extension \"mysql\" not loaded... Cannot work ....");
            return false;
 	}
	
 	# connecting to server
	public function connect($serv,$port, $db, $user, $passwd){
		if( $port!=null){
			$serv=$serv.":".$port;
		}		
		if ($this->core->get("dbopersistent")==true){ 
			$this->rConnection = @mysql_pconnect($serv, $user, $passwd);
		} else {
			$this->rConnection = @mysql_connect($serv, $user, $passwd);
		}
		if (!is_resource($this->rConnection)){
			$this->core->error("DBO_DRIVER_CONNECT", "CANNOT CONNECT TO THE DATABASE SERVER. CHECK PARAMETERS PLEASE.");
		return false;
		} else {
			if(!mysql_select_db($db, $this->rConnection)){
				$this->core->error("DBO_DRIVER_CONNECT", "CANNOT SELECT DATABASE ON SQL SERVER. CHECK PARAMETERS PLEASE.");
			return false;
			}
		$this->execute("set names utf8");
		}
		$this->database = $db; 
	}
	
	# disconnecting from server
	public function disconnect(){
		if (is_resource($this->rConnection)){
			mysql_close($this->rConnection);
		}
	}
	
	# main sql select method with result handling
	public function select($table, $fields = "*", $sql = null, $type = _RESULT_ASSOC){
		$sql = "select " . $fields . " from " . $table . " " . $sql;
		
		$q = mysql_query($sql, $this->rConnection);
		if ($q == false){
			$this->core->error("DBO_DRIVER_SELECT", (is_resource($this->rConnection) ? mysql_error($this->rConnection) : null));
			return false;
		}
		switch ($type){
            case _RESULT_ASSOC:
                return $this->sqlToAssoc($q);
                break;
            case _RESULT_LIST:
                return $this->sqlToList($q, $fields);
                break;
            case _RESULT_BOOL:
                return ((bool) mysql_num_rows($q));
                break;
            case _RESULT_PAIRS:
                return $this->sqlToPairs($q);
                break;
            case _RESULT_NUMROW:
                return $this->sqlToNumRow($q);
                break;		
            case _RESULT_ROW:
                return $this->sqlToRow($q);
                break;
            case _RESULT_SINGLE:
	    	case _RESULT_ONE:
	    		return $this->sqlToSingle($q);
	    		break;	    
            default:
                return $this->sqlToAssoc($q);
        }
	}
	
	# sql result to simple array ( as list )
	private function &sqlToList(&$q, $fields){
		if (preg_match("/[,]/", $fields, $null)){
			$this->core->error("DBO_DRIVER_SELECT", "Error in results ordering as LIST. Field should be without comas.");
		return false;
		}
		$a = array();
		while($row=mysql_fetch_row($q)){
			$a[] = $row[0];
		}
		mysql_free_result($q);
		return $a;
	}


	private function sqlToSingle(&$q){
    	if( mysql_num_rows($q) == 1 && mysql_num_fields($q)== 1 ){
    		$q=mysql_fetch_row($q);
    		return $q[0];
    	}
    	mysql_free_result($q);
    	return null; 
    }

    # sql select result as associative array ( like dictionary in python )
	private function sqlToRow(&$q){
		if (mysql_num_rows($q) == 0)
			return array();
		return mysql_fetch_assoc($q);
	}

	# sql select result as numeric indexes array
	private function sqlToNumRow(&$q){
		if (mysql_num_rows($q) == 0)
			return array();
		return mysql_fetch_row($q);
	}

	# sql select result to array of pairs key=>value
	private function &sqlToPairs(&$q){
		$array = array();
		if (mysql_num_fields($q) != 2){
			$this->core->error("DBO", "Cannot build pairs for result. Fields number must be 2");
		return false;
		}
		while ($row = mysql_fetch_row($q)){
			$array[$row[0]] = $row[1];
		}
		mysql_free_result($q);
		return $array;
	}
	# sql select result to arrays of associative array
	private function &sqlToAssoc(&$q){
		$array = array();
		while($row =mysql_fetch_assoc($q)) {
			$array[] = $row;
		}
		mysql_free_result($q);
		return $array;
	}
	# inserting records to sql table
	public function insert($table, array $data,$returnid=null){
		if (!is_array($data) || count($data) < 1){
			return false;
		}
		$q = @mysql_query(  "INSERT INTO `$table` (`".implode("`,`",array_keys($data))."`) VALUES ('".implode("','",array_values($data))."')" , $this->rConnection);
		if (!$q){
			$this->core->error("DBO_DRIVER_INSERT", mysql_error($this->rConnection));
		return false;
		}
		if( $returnid!=null) {
			return mysql_insert_id($this->rConnection);
		}
		return true;
	}
	# updating table
	public function update($table, $sql){
		$sql = "update " . $table . " " . $sql;
		$q = @mysql_query($sql, $this->rConnection);
		if (!$q) {
			$this->core->error("DBO_DRIVER_UPDATE", mysql_error($this->rConnection));
		return false;
		}
		return true;
	}
    # deleting from table  
	public function delete($table, $sql){
		$sql = "delete from " . $table . " " . $sql;
		$q = @mysql_query($sql, $this->rConnection);
		if ($q == false){
			$this->core->error("DBO_DRIVER_DELETE", mysql_error($this->rConnection));
		}
		return true;
	}
	# direct sql query
	public function execute($sql){
		$q = @mysql_query($sql, $this->rConnection);
		if ( !$q){
			$this->core->error("DBO_DRIVER_DIRECT", mysql_error($this->rConnection));
			return false;
		}
		return $q;
	}

	# getting mysql server information
    public function version(){
    	return mysql_get_server_info();
    }
	
    # creating sql table
    public function createtable(sqltable $map){
		
      	list($name,$fields,$keys,$encoding,$engine) = $map->get();
        $pkeys =array();
      	foreach( $keys as $k=>$v ){
      		if( $v[0]==_KEY_PRIMARY) $pkeys[]=$v[1];
      	}
      	$text = "create table `$name` ( ";
      	# fields processing      	
      	foreach( $fields as $k=>$v){
      		list($fname,$ftype,$fsize,$fdefault,$fnull) = $v;
			if( ( $ftype==_FIELD_INTAUTO || $ftype==_FIELD_BIGINTAUTO) && !in_array($fname,$pkeys) ){
				$pkeys[]=$fname;
			}
      	    if( $ftype==_FIELD_TEXT && is_null($fdefault)) {
      			$default ="  ";
      		}else if( ( $ftype==_FIELD_INT || $ftype==_FIELD_BIGINT || $ftype==_FIELD_TINYINT )  && is_null($fdefault)){
      			$default =" DEFAULT '0' ";
      		}
      		else {
      			$default =  !is_null($fdefault)?" DEFAULT '".$this->core->escapeString($fdefault)."' ":" ".($fnull?"NULL":"NOT NULL")."  ";
      		}
      		$fresult = $this->type_def($ftype,$fsize);
      		$text .=($k==0?null:" ,")." `$fname` $fresult ".$default." ";
      	}
      	if( count($pkeys)>0){
      		$text .=", PRIMARY KEY ( `".implode("`,`",$pkeys)."` ) ";
      	}
      	$text .=" ) ENGINE=".(  is_null($engine)?$this->DefaultEngine:$engine)." ".
      	( is_null($encoding)?" DEFAULT CHARSET='".$this->DefaultCharset:$encoding)."' COMMENT='Created with Gepard v".$this->core->GetVersion()."'; " ;    	
      	# text is ready for table execution
      	if( $map->droponexist){
      		$this->execute("drop table if exists `$name` ");
      	}     	
      	$this->execute($text);  	
      	# keys processing
      	foreach( $keys as $k=>$v){
      		$query = null;
      		list($ktype,$kfield) = $v;
      		$keydata = null;      		
      		switch( strtolower($ktype) ){
      			case _KEY_INDEX  : $keydata = "INDEX"; break;
      			case _KEY_UNIQUE : $keydata = "UNIQUE"; break;
      			case _KEY_FULLTEXT: $keydata = "FULLTEXT"; break;     			
      		}    		
      		if( !is_null($keydata)){
      			
      			$query = "ALTER TABLE `$name` ADD $keydata ( `".$kfield."` ); ";
      			$this->execute($query);
      		}
      	}
      	return true;
	}
    
	# dropping sql table
    public function droptable($table){
    	if( is_object($table) && get_class($table)=="sqltable"){
    		$table = $table->getname();
    	}
    	$this->execute("drop table if exists `".$table."`");
    	return true;
    }
    
    # listing tables of current database
    public function gettables($database=null){
    	if( $database==null)$database=$this->database;
    	$sql = "SHOW TABLES FROM ".$database;
		$r = mysql_query($sql,$this->rConnection);
		$tables = array();
		while( $row = mysql_fetch_row($r)){
			$tables[]=$row[0];
		}
		mysql_free_result($r);
      	return $tables;
    }
    
    # getting table fields ( name, type, size, default, additional)
    public function getfields($table){
		# escaping disabled charcters of tablename
		$table = $this->core->escapeString($table);    	
    	$fiels = array();
		# trying to make a query    	
		$result = $this->execute("SHOW COLUMNS FROM ".$table);
		if (mysql_num_rows($result) > 0) {
				while ($row = mysql_fetch_assoc($result)) {
					$null = ($row["Null"]=="YES")?true:false;
					list($type,$size)=$this->gettypesize($row["Type"]);
					if( preg_match("/auto_increment/",$row["Extra"]))$type=( $type==_FIELD_BIGINT?_FIELD_INTAUTO:_FIELD_BIGINTAUTO);
					$fields[]=array("name"=>$row["Field"],"type"=>$type,"size"=>$size,"default"=>$row["Default"],"null"=>$null);
			}
		}
		return $fields;
    }
    /*
     * getfields service methods 
     */
    private function gettypesize($type){
    	if( preg_match("/^([A-z0-9_]+)\(([0-9\,]+)\)$/",$type,$match)){
    		$type = $this->type_defback($match[1]);
    		$size = $match[2];
    		
    	} else {
    		$type = $this->type_defback($type);
    		$size = null;
    	}
    	if( preg_match("/^decimal/",$type)){
    		$type=_FIELD_DECIMAL;
    	}
    	return array($type,$size);
    }
    
    # getting list of databases on alive connection
    public function getdatabases(){
    	# query for list of databases
    	$dblist = $this->execute("show databases");
    	$list = array();
		while( $row = mysql_fetch_assoc($dblist)){
			$list[] = $row["Database"];
		}
    	return $list;
    }
    
    # getting current workdatabase
    public function getdatabase(){
    	return $this->database;	
    }    
    # sql database types from defines converting
    private function type_def($type, $size = null)
      {
        $result = null;
        switch ($type)
        {
            case _FIELD_INTAUTO:
                $result = "INT(".($size != null ? (int) $size : "11").") AUTO_INCREMENT";
                break;
            case _FIELD_BIGINTAUTO:
                $result = "BIGINT(".($size != null ?(int)$size : "20"). ") AUTO_INCREMENT";
                break;
            case _FIELD_INT:
                $result = "INT(" . ($size != null ? (int) $size : "11") . ") ";
                break;
            case _FIELD_TINYINT:
                $result = "TINYINT(" . ($size != null ? (int) $size : "4") . ") ";
                break;
            case _FIELD_BIGINT:
                $result = "BIGINT(" . ($size != null ? (int) $size : "20") . ") ";
                break;
            case _FIELD_SMALLINT:
                $result = "SMALLINT(" . ($size != null ? (int) $size : "6") . ") ";
                break;
            case _FIELD_CONSTCHAR:
                $result = "CHAR(" . ($size != null ? (int) $size : "255") . ") ";
                break;          	
            case _FIELD_CHAR:
                $result = "VARCHAR(" . ($size != null ? (int) $size : "255") . ") ";
                break;
            case _FIELD_DATE:
                $result = "DATE ";
                break;
            case _FIELD_DATETIME:
                $result = " DATETIME";
                break;
            case _FIELD_TIMESTAMP:
                $result = " TIMESTAMP";
                break;
            case _FIELD_TIME:
                $result = " TIME";
                break;
            case _FIELD_FLOAT:
            	$result = "FLOAT".($size != null?"(".$size.")":null);
            	break;
            case _FIELD_DECIMAL:
            	$result = "DECIMAL(".($size != null ? $size : "10,5").")";
            	break;
            case _FIELD_REAL:
            	$result = "REAL".($size != null?"(".$size.")":null);
            	break;
            case _FIELD_DOUBLE:
            	$result = "DOUBLE".($size != null?"(".$size.")":null);
            	break;
            case _FIELD_BIT:
            	$result = "BIT";
            	break;
            case _FIELD_BOOL:
            	$result = " BOOL ";
            	break;
            case _FIELD_BLOB:
            	$result = " BLOB ";
            	break;
            case _FIELD_TEXT:
                $result = "TEXT";
                break;
            case _FIELD_LONGTEXT:
                $result = "LONGTEXT";
                break;
            default:
                $result=$type;
                break;
        }
        return $result;
      }
      
      private function type_defback($type){
        $result = null;
        switch ($type)
        {
        	case "smallint":
        		$result = _FIELD_SMALLINT;
        		break;
        	case "mediumint":
            case "int":
                $result = _FIELD_INT;
                break;
            case "tinyint":
                $result = _FIELD_TINYINT;
                break;
            case "bigint":
                $result = _FIELD_BIGINT;
                break;
            case "char":
            	$result = _FIELD_CONSTCHAR;
            	break;
            case "varchar":
                $result = _FIELD_CHAR;
                break;
            case "date":
                $result = _FIELD_DATE;
                break;
            case "datetime":
                $result = _FIELD_DATETIME;
                break;
            case "timestamp":
                $result = _FIELD_TIMESTAMP;
                break;
            case "time":
                $result = _FIELD_TIME;
                break;
            case "float":
            	$result = _FIELD_FLOAT;
            	break;
            case "real":
            	$result = _FIELD_REAL;
            	break;
            case "double":
            	$result = _FIELD_DOUBLE;
            	break;
            case "decimal":
            	$result = _FIELD_DECIMAL;
            	break;
            case "bool":
            	$result = _FIELD_BOOL;
            	break;
            case "blob":
            	$result = _FIELD_BLOB;
            	break;
            case "bit":
            	$result = _FIELD_BIT;
            	break;
            case "tinytext":
            case "mediumtext":
            case "text":
                $result = _FIELD_TEXT;
                break;
            case "logtext":
                $result = _FIELD_LONGTEXT;
                break;
            default:
                $result=$type;
                break;
        }
        return $result;
      }
      
      # checking if sql table exists in current working database
      public function tableexists($table){
      	$sql = "SHOW TABLES FROM ".$this->database;
		$r = mysql_query($sql,$this->rConnection);
		$tables = array();
		while( $row = mysql_fetch_row($r)){
			$tables[]=$row[0];
		}
		mysql_free_result($r);
      	return in_array($table,$tables);
      }
      
  }
?>